package com.kb.mapper.pg;


import com.kb.dto.flow.*;
import com.kb.dto.HistoricalFactory;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface FlowMapper {


    @Select({
            "<script>",
            "SELECT TOP 100 KB_Trade_Route.Prod_Org_S AS historicalFactory, data0050.CUSTOMER_PART_NUMBER AS code ",
            "FROM MSSQLLIVE.dbo.data0060 WITH (NOLOCK) ",
            "INNER JOIN MSSQLLIVE.dbo.data0050 WITH (NOLOCK) ON data0060.CUST_PART_PTR = data0050.rkey ",
            "LEFT JOIN MSSQLLIVE.dbo.KB_Trade_Route WITH (NOLOCK) ON data0060.Trade_Route_ptr = KB_Trade_Route.rkey ",
            "<if test='keyword != null and keyword != \"\"'>",
            "WHERE data0050.CUSTOMER_PART_NUMBER LIKE CONCAT( #{keyword}, '%')",
            "</if>",
            "</script>"
    })
    List<HistoricalFactory> queryBankCard(@Param("keyword") String keyword);

    @Select({
            "<script>",
            "SELECT TOP 50 KB_Trade_Route.Prod_Org_S AS historicalFactory, pcba_order.Partnumber AS code ",
            "FROM MSSQLLIVE.dbo.pcba_order WITH (NOLOCK) ",
            "LEFT JOIN MSSQLLIVE.dbo.KB_Trade_Route WITH (NOLOCK) ON pcba_order.TRADE_Route_ptr = KB_Trade_Route.rkey ",
            "<if test='keyword != null and keyword != \"\"'>",
            "WHERE pcba_order.gc = 'FPC' AND pcba_order.Partnumber LIKE CONCAT( #{keyword}, '%') ",
            "</if>",
            "</script>"
    })
    List<HistoricalFactory> queryBankCardTwo(@Param("keyword") String keyword);

    @Select({
            "<script>",
            "SELECT TOP 50",
            "CASE ",
            "   WHEN data0439.order_type_code IN ('ZCFT','ZCXT','ZCFG') THEN '泽创' ",
            "   WHEN data0439.order_type_code IN ('KBFT','KBXT','KBFG') THEN '惠州'  ",
            "   ELSE data0015.ABBR_NAME ",
            "END AS historicalFactory, ",
            "data0050.CUSTOMER_PART_NUMBER AS code ",
            "FROM MSSQLLIVE.dbo.data0060 WITH (NOLOCK) ",
            "INNER JOIN MSSQLLIVE.dbo.data0050 WITH (NOLOCK) ON data0060.CUST_PART_PTR = data0050.rkey ",
            "LEFT JOIN MSSQLLIVE.dbo.data0439 WITH (NOLOCK) ON data0060.ORD_TYPE_PTR = data0439.rkey ",
            "LEFT JOIN MSSQLLIVE.dbo.data0015 WITH (NOLOCK) ON data0060.WAREHOUSE_PTR = data0015.rkey ",
            "<if test='keyword != null and keyword != \"\"'>",
            "WHERE data0050.CUSTOMER_PART_NUMBER LIKE CONCAT( #{keyword}, '%') AND data0050.CUSTOMER_PART_NUMBER IS NOT NULL OPTION (RECOMPILE)",
            "</if>",
            "</script>"
    })
    List<HistoricalFactory> queryBankCardThree(@Param("keyword") String keyword);


    @Select({
            "<script>",
            "SELECT t_cust_comp.ord_no AS ord_no, " +
                    "       t_cust_comp.comp_number AS comp_number, " +
                    "       t_cust_comp.handle_nick_name AS handle_nick_name " +
                    "FROM t_cust_comp " +
                    "<if test='keyword != null and keyword != \"\"'> " +
                    "WHERE t_cust_comp.comp_number LIKE CONCAT('%', #{keyword}, '%') " +
                    "</if>",
            "</script>"
    })
    List<DisposalOrderNumber> queryCustomerComplaint(@Param("keyword") String keyword);

    @Select(
            "SELECT TOP(50) FYEAR AS fYear,FDETAILID as fId, FPERIOD AS fPeriod, A.FEndBalance AS fEndBalance, G.FNUMBER AS fNumber, G1.FNAME AS fName, C.FNUMBER AS custCode,A.fendBalanceFor AS fendBalanceFor, C1.FNAME AS custName "+
                    "FROM AIS20160407095349.DBO.T_GL_Balance A WITH(NOLOCK) " +
                    "LEFT JOIN AIS20160407095349.DBO.T_BD_ACCOUNTBOOK F WITH(NOLOCK) ON F.FBOOKID = A.FACCOUNTBOOKID " +
                    "LEFT JOIN AIS20160407095349.DBO.T_ORG_ORGANIZATIONS G WITH(NOLOCK) ON F.FACCOUNTORGID = G.FORGID " +
                    "LEFT JOIN AIS20160407095349.DBO.T_ORG_ORGANIZATIONS_L G1 WITH(NOLOCK) ON F.FACCOUNTORGID = G1.FORGID AND G1.FLOCALEID='2052' " +
                    "LEFT JOIN AIS20160407095349.DBO.T_BD_ACCOUNT e WITH(NOLOCK) ON A.FACCOUNTID = e.FACCTID " +
                    "LEFT JOIN AIS20160407095349.DBO.T_BD_FLEXITEMDETAILV d WITH(NOLOCK) ON A.FDETAILID = d.FID " +
                    "LEFT JOIN AIS20160407095349.DBO.T_BD_CUSTOMER C WITH(NOLOCK) ON D.FFLEX6 = C.FCUSTID " +
                    "LEFT JOIN AIS20160407095349.DBO.T_BD_CUSTOMER_L C1 WITH(NOLOCK) ON D.FFLEX6 = C1.FCUSTID " +
                    "WHERE FYEAR = YEAR(DATEADD(MONTH, -1, GETDATE())) AND FPERIOD = MONTH(DATEADD(MONTH, -1, GETDATE())) " +
                    "AND E.FNUMBER = '1122.01' AND A.FCURRENCYID <> 0 AND A.FEndBalance <> 0 AND FDETAILID <> 0 " +
                    "AND  C.FNUMBER LIKE CONCAT('%', #{keyword}, '%')"

    )
    List<BalanceReceivable> getBalanceReceivable(@Param("keyword") String keyword);

    @Select({
            "SELECT INVT_NUMBER, INVT_NAME, INVT_MODEL, price ",
            "FROM ITMANAGE.dbo.INVT_INFO WITH (NOLOCK) ",
            "WHERE TTYPE = 2 ",
            "AND REC_DATE >= '2025-04-20' ",
            "AND (",
            "    INVT_NAME LIKE CONCAT('%', #{keyword}, '%') ",
            "    OR INVT_NUMBER LIKE CONCAT('%', #{keyword}, '%') ",
            ")"
    })
    List<PartsConsumables> getITPartsConsumables(@Param("keyword") String keyword);

    @Select({
            "<script>",
            "SELECT",
            "   C.CustCode,",
            "   C.CustName,",
            "   U.username,",
            "   SUM(A.totalAmount) / 1.13 / 10000 AS ConvertedAmount",
            "FROM",
            "   SmartQuote_kb_live.dbo.Customer C",
            "LEFT JOIN",
            "   SmartQuote_kb_live.dbo.Xnh_UserInfo U ON U.rkey = C.sysuserId",
            "LEFT JOIN",
            "   [SmartQuote_kb_live].[dbo].[CRM_SalesOrder] A ON C.CustCode = A.[custCode]",
            "WHERE",
            "   C.status = 'Valid'",

            "<if test='keyword != null and keyword != \"\"'>",
//            "   AND (C.CustCode LIKE CONCAT('%', #{keyword}, '%') OR C.CustName LIKE CONCAT('%', #{keyword}, '%'))",
            "   AND C.CustCode = #{keyword}",
            "</if>",

            "<if test='userName_eq != null and userName_eq != \"\"'>",
            "   AND U.username = #{userName_eq}",
            "</if>",

            "   AND A.poDate &gt;= '2024-01-01'",
            "   AND A.poDate &lt;= '2024-12-30'",
            "   AND A.status != '取消'",

            "GROUP BY",
            "   C.CustCode,",
            "   C.CustName,",
            "   U.username",
            "</script>"
    })
    List<CrmCustomerInfo> getCrmCustomerInfo(
            @Param("keyword") String keyword,
            @Param("userName_eq") String userName_eq);
}
